package com.laboratory.util.office;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

	
	
	
	/**
	 * Excel2007后的版本，扩展名是.xlsx
	 * @param filePath
	 * @param resultList
	 */
	public static void readExcel2007(String filePath,String sheetName, List<List<String>> resultList) throws Exception{
		if(filePath == null || filePath == "") throw new Exception(filePath + "文件路径为空");
		if(!filePath.endsWith(".xlsx")) throw new Exception(filePath + "文件类型不是.xlsx");
		
		FileInputStream  fileInputStream= new FileInputStream(new File(filePath));
		XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); 

		XSSFSheet sheet = workbook.getSheet(sheetName); 
		for(int rowNumber = 0; rowNumber<= sheet.getLastRowNum(); rowNumber++){
			XSSFRow row = sheet.getRow(rowNumber);
			List<String> rowValueList =  new ArrayList<>();
			for(int cellNumber = 0; cellNumber< row.getLastCellNum(); cellNumber++ ){				
				XSSFCell cell = row.getCell(cellNumber);
				if (null == cell) continue;
				String cellValue = getValue(cell);
				System.out.println(cellValue);
				rowValueList.add(cellValue);
								
			}
			resultList.add(rowValueList);
											
		}
		workbook.close();
		fileInputStream.close();
	}
	/**
	 * Excel2003以前（包括2003）的版本，扩展名是.xls
	 * @param filePath
	 * @param sheetName
	 * @param resultList
	 * @throws Exception
	 */
	public static void readExcel2003(String filePath,String sheetName, List<List<String>> resultList) throws Exception{
		if(filePath == null || filePath == "") throw new Exception(filePath + "文件路径为空");
		if(!filePath.endsWith(".xls")) throw new Exception(filePath + "文件类型不是.xls");
		
		FileInputStream  fileInputStream= new FileInputStream(new File(filePath));
		HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); 

		HSSFSheet sheet = workbook.getSheet(sheetName); 
		for(int rowNumber = 0; rowNumber<= sheet.getLastRowNum(); rowNumber++){
			HSSFRow row = sheet.getRow(rowNumber);
			List<String> rowValueList =  new ArrayList<>();
			for(int cellNumber = 0; cellNumber< row.getLastCellNum(); cellNumber++ ){				
				HSSFCell cell = row.getCell(cellNumber);
				String cellValue = cell.getStringCellValue();
				rowValueList.add(cellValue);		
								
			}
			resultList.add(rowValueList);
											
		}
						
	}
	/**
	 * 得到单元格的值
	 * @param HSSFCell cell
	 * @return cell单元格的值
	 */
	private static String getValue(XSSFCell cell) {
		if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
			// boolean类型
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
			// 数字类型
			return String.valueOf(cell.getNumericCellValue());
		} else {
			// String类型
			return String.valueOf(cell.getStringCellValue());
		}
	}

	/**
	 *
	 * @param resultList
	 * @param filePath 例如：E:\文件\批量导入问题\节点2.xlsx
	 * @throws Exception
	 */
	public static void writeExcel2007(List<List<String>> resultList, String filePath) throws Exception{

		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet sheet = workBook.createSheet();

		for(int i= 0 ; i<resultList.size() ; i++){
			XSSFRow row = sheet.createRow(i);
			List<String> StrList = resultList.get(i);
			for (int j = 0 ; j<StrList.size(); j++){
				XSSFCell cell = row.createCell(j);
				cell.setCellType(cell.CELL_TYPE_STRING);
				cell.setCellValue(StrList.get(j));
			}

		}
		FileOutputStream outputStream = new FileOutputStream(filePath);
		workBook.write(outputStream);

	}

	/**
	 *
	 * @param resultList
	 * @param filePath 例如：E:\文件\批量导入问题\节点2.xlsx
	 * @throws Exception
	 */
	public static void writeExcel2007BigData(List<List<String>> resultList, String filePath) throws Exception{
		SXSSFWorkbook workBook = new SXSSFWorkbook();
		Sheet sheet = workBook.createSheet();

		for(int i= 0 ; i<resultList.size() ; i++){
			Row row = sheet.createRow(i);
			List<String> StrList = resultList.get(i);
			for (int j = 0 ; j<StrList.size(); j++){
				Cell cell = row.createCell(j);
				cell.setCellType(cell.CELL_TYPE_STRING);
				cell.setCellValue(StrList.get(j));
			}

		}
		FileOutputStream outputStream = new FileOutputStream(filePath);
		workBook.write(outputStream);

	}


	public static void  checkExcel2007(String filePath,String sheetName) throws Exception{
		if(filePath == null || filePath == "") throw new Exception(filePath + "文件路径为空");
		if(!filePath.endsWith(".xlsx")) throw new Exception(filePath + "文件类型不是.xlsx");

		FileInputStream  fileInputStream= new FileInputStream(new File(filePath));
		XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

		XSSFSheet sheet = workbook.getSheet(sheetName);
		for(int rowNumber = 0; rowNumber<= sheet.getLastRowNum(); rowNumber++){
			XSSFRow row = sheet.getRow(rowNumber);
			List<String> rowValueList =  new ArrayList<>();
			for(int cellNumber = 0; cellNumber< row.getLastCellNum(); cellNumber++ ){
				XSSFCell cell = row.getCell(cellNumber);
				if (null == cell) continue;
				String cellValue = getValue(cell);
				System.out.println(cellValue);
				rowValueList.add(cellValue);

			}


		}
		workbook.close();
		fileInputStream.close();
	}

	
	public static void main(String[] args) throws Exception {
	/*	String filePath = "E:\\文件\\批量导入问题\\1.xlsx";
		HashMap<String, String> resultMap = new HashMap<>();
		List<List<String>> resultList = new ArrayList<>();
		readExcel2007(filePath, "Sheet1", resultList);
		for(int i = 0; i<resultList.size(); i++){
			List<String> result = resultList.get(i);
			resultMap.put(result.get(0), result.get(1));
		}

		Map<String, String> deptMapper = new HashMap<>();
		List<List<String>> deptNmIdMapper = new ArrayList<>();
		readExcel2007(filePath, "Sheet2", deptNmIdMapper);
		for(int i = 0; i<deptNmIdMapper.size(); i++){
			List<String> result = deptNmIdMapper.get(i);
			deptMapper.put(result.get(0), result.get(1));
		}
		HashMap<String, String> nodeMap = new HashMap<>();
		List<List<String>> NodeeList = new ArrayList<>();
		readExcel2007("E:\\文件\\批量导入问题\\业务归属部门为空-附件未包含-从导入日志查到归属部门.xlsx", "sheet1", NodeeList);
		System.out.println(NodeeList.size());
		for(int i = 0; i<NodeeList.size(); i++){
			String NodeNm = NodeeList.get(i).get(0);
			String deptNm = NodeeList.get(i).get(1);
			System.out.println(NodeNm +" "+deptNm +" "+ deptMapper.get(deptNm));
			NodeeList.get(i).add(NodeNm==null?"":deptMapper.get(deptNm));
		}
		writeExcel2007(NodeeList, "E:\\文件\\批量导入问题\\业务归属部门为空-附件未包含-从导入日志查到归属部门2.xlsx");

*/
		String filePath = "E:\\文件\\批量导入问题\\1.xlsx";
		HashMap<String, String> resultMap = new HashMap<>();
		List<List<String>> resultList = new ArrayList<>();
		checkExcel2007(filePath, "Sheet1");

	}

}
